"""
Case Type   : 系统视图PG_PREPARED_STATEMENT
Case Name   : 传入引号包裹的session id，查询成功，会隐式转换
Create At   : 2023/12/1
Owner       : ningyali
Description :
    1.建表
    2.新建session1，创建预备语句
    3.获取session1的session_id
    4.传入session1的session_id使用PG_PREPARED_STATEMENT函数查询
    5.清理环境
Expect      :
    1.执行成功
    2.执行成功
    3.执行成功
    4.执行成功，查询结果中有stmt1
    5.执行成功
History     :
"""

import os
import time
import unittest

from testcase.utils.ComThread import ComThread
from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class PgPreparedStatement8(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        text = f'-----{os.path.basename(__file__)} start-----'
        self.log.info(text)
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.common = Common()
        self.t_name = 't_prepare_08'
        self.prepare_name = 'stmt1'
        self.prepare_sql = f"prepare {self.prepare_name} as " \
            f"insert into {self.t_name} values(1, 'openGauss');"

    def test_1(self):
        text = 'step1:建表; expect:执行成功'
        self.log.info(f'-----{text}-----')
        sql = f'drop table if exists {self.t_name};' \
            f'create table {self.t_name}(c1 int, c2 text);'
        self.log.info(sql)
        res = self.pri_sh.execut_db_sql(sql)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, res, f'执行失败：{text}')

        text = 'step2:新建session1，创建预备语句; expect:执行成功'
        self.log.info(f'-----{text}-----')
        session1 = ComThread(self.pri_sh.execut_db_sql,
                             args=(f'{self.prepare_sql}'
                                   f'select pg_sleep(10);', ))
        session1.setDaemon(True)
        session1.start()
        time.sleep(1)

        text = 'step3:获取session1的session_id; expect:执行成功'
        self.log.info(f'-----{text}-----')
        sql = f"SELECT sa.sessionid AS sid, 0::integer AS serial#, " \
            f"ad.rolname AS username, sa.backend_start as backend_start " \
            f"FROM pg_stat_get_activity(NULL) AS sa " \
            f"LEFT JOIN pg_authid ad " \
            f"ON(sa.usesysid=ad.oid) " \
            f"WHERE sa.application_name <>'JobScheduler' " \
            f"order by 4 desc limit 1 offset 1;"
        self.log.info(sql)
        res = self.pri_sh.execut_db_sql(sql)
        rows, _ = self.common.parse_sql_query_res(res)

        text = 'step3:新建session2，传入session1的session_id' \
               '使用PG_PREPARED_STATEMENT函数查询; ' \
               'expect:执行成功，查询结果中没有stmt1'
        self.log.info(f'-----{text}-----')
        query_sql = f"select * from pg_prepared_statement('{rows[0][0]}') " \
            f"order by 1, 2;"
        self.log.info(query_sql)
        res = self.pri_sh.execut_db_sql(f'{query_sql}')
        self.log.info(res)
        self.assertIn(self.prepare_name, res, f'执行失败：{text}')
        self.assertIn(self.prepare_sql, res, f'执行失败：{text}')

        text = '获取session1结果'
        self.log.info(f'-----{text}-----')
        session1.join(30)
        res = session1.get_result()
        self.log.info(res)
        self.assertIn('PREPARE', res, f'执行失败：{text}')

    def tearDown(self):
        self.log.info('----run teardown----')
        text = 'step4:清理环境; expect:执行成功'
        self.log.info(f'-----{text}-----')
        sql = f'drop table if exists {self.t_name};'
        res = self.pri_sh.execut_db_sql(sql)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, res, f'执行失败：{text}')
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
